#! /bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive


${HIVE_HOME} -S -e "

if [[ $1 == "" ]];then
   TD_DATE=`date -d '1 days ago' "+%Y-%m-%d"`
else
   TD_DATE=$1
fi

--创建临时表
drop table if exists zxedu_dwd.fact_customer_relationship_tmp;
create table zxedu_dwd.fact_customer_relationship_tmp
(   id                          int,
    create_date_time            string,
    update_date_time            string     COMMENT '最后更新时间',
    deleted                     string     COMMENT '是否被删除（禁用）',
    customer_id                 INT        COMMENT '所属客户id',
    first_id                    INT        COMMENT '第一条客户关系id',
    belonger                    INT        COMMENT '归属人',
    belonger_name               string     COMMENT '归属人姓名',
    initial_belonger            INT        COMMENT '初始归属人',
    distribution_handler        INT        COMMENT '分配处理人',
    business_scrm_department_id INT        COMMENT '归属部门',
    last_visit_time             string     COMMENT '最后回访时间',
    next_visit_time             string     COMMENT '下次回访时间',
    origin_type                 string     COMMENT '数据来源',
    itcast_school_id            INT        COMMENT '校区Id',
    itcast_subject_id           INT        COMMENT '学科Id',
    intention_study_type        string     COMMENT '意向学习方式',
    anticipat_signup_date       string     COMMENT '预计报名时间',
    level                       string     COMMENT '客户级别',
    creator                     INT        COMMENT '创建人',
    current_creator             INT        COMMENT '当前创建人：初始==创建人，当在公海拉回时为 拉回人',
    creator_name                string     COMMENT '创建者姓名',
    origin_channel              string     COMMENT '来源渠道',
    COMMENT                     string,
    first_customer_clue_id      INT        COMMENT '第一条线索id',
    last_customer_clue_id       INT        COMMENT '最后一条线索id',
    process_state               string     COMMENT '处理状态',
    process_time                string     COMMENT '处理状态变动时间',
    payment_state               string     COMMENT '支付状态',
    payment_time                string     COMMENT '支付状态变动时间',
    signup_state                string     COMMENT '报名状态',
    signup_time                 string     COMMENT '报名时间',
    notice_state                string     COMMENT '通知状态',
    notice_time                 string     COMMENT '通知状态变动时间',
    lock_state                  string     COMMENT '锁定状态',
    lock_time                   string     COMMENT '锁定状态修改时间',
    itcast_clazz_id             INT        COMMENT '所属ems班级id',
    itcast_clazz_time           string     COMMENT '报班时间',
    payment_url                 string     COMMENT '付款链接',
    payment_url_time            string     COMMENT '支付链接生成时间',
    ems_student_id              INT        COMMENT 'ems的学生id',
    delete_reason               string     COMMENT '删除原因',
    deleter                     INT        COMMENT '删除人',
    deleter_name                string     COMMENT '删除人姓名',
    delete_time                 string     COMMENT '删除时间',
    course_id                   INT        COMMENT '课程ID',
    course_name                 string     COMMENT '课程名称',
    delete_comment              string     COMMENT '删除原因说明',
    close_state                 string     COMMENT '关闭装填',
    close_time                  string     COMMENT '关闭状态变动时间',
    appeal_id                   INT        COMMENT '申诉id',
    tenant                      INT        COMMENT '租户',
    total_fee                   DECIMAL(19)COMMENT '报名费总金额',
    belonged                    INT        COMMENT '小周期归属人',
    belonged_time               string     COMMENT '归属时间',
    belonger_time               string     COMMENT '归属时间',
    transfer                    INT        COMMENT '转移人',
    transfer_time               string     COMMENT '转移时间',
    follow_type                 INT        COMMENT '分配类型，0-自动分配，1-手动分配，2-自动转移，3-手动单个转移，4-手动批量转移，5-公海领取',
    transfer_bxg_oa_account     string     COMMENT '转移到博学谷归属人OA账号',
    transfer_bxg_belonger_name  string     COMMENT '转移到博学谷归属人OA姓名',
    year_code                   string     comment '数据创建年份',
    month_code                  string     comment '数据创建年月',
    date_code                   string     comment '数据创建日期',
    end_date                    string     COMMENT '拉链结束时间')
COMMENT '客户意向表临时表'
partitioned by (start_date string) row format delimited fields terminated by '\t'
stored as orc tblproperties ('orc.compress'='SNAPPY');
--合并新旧数据插入到临时表
insert overwrite table zxedu_dwd.fact_customer_relationship_tmp partition(start_date)
select d.id,
       d.create_date_time,
       d.update_date_time,
       d.deleted,
       d.customer_id,
       d.first_id,
       d.belonger,
       d.belonger_name,
       d.initial_belonger,
       d.distribution_handler,
       d.business_scrm_department_id,
       d.last_visit_time,
       d.next_visit_time,
       d.origin_type,
       d.itcast_school_id,
       d.itcast_subject_id,
       d.intention_study_type,
       d.anticipat_signup_date,
       d.level,
       d.creator,
       d.current_creator,
       d.creator_name,
       d.origin_channel,
       d.comment,
       d.first_customer_clue_id,
       d.last_customer_clue_id,
       d.process_state,
       d.process_time,
       d.payment_state,
       d.payment_time,
       d.signup_state,
       d.signup_time,
       d.notice_state,
       d.notice_time,
       d.lock_state,
       d.lock_time,
       d.itcast_clazz_id,
       d.itcast_clazz_time,
       d.payment_url,
       d.payment_url_time,
       d.ems_student_id,
       d.delete_reason,
       d.deleter,
       d.deleter_name,
       d.delete_time,
       d.course_id,
       d.course_name,
       d.delete_comment,
       d.close_state,
       d.close_time,
       d.appeal_id,
       d.tenant,
       d.total_fee,
       d.belonged,
       d.belonged_time,
       d.belonger_time,
       d.transfer,
       d.transfer_time,
       d.follow_type,
       d.transfer_bxg_oa_account,
       d.transfer_bxg_belonger_name,
       year_code,
       month_code,
       date_code,
       if(d.id=o.id and d.end_date = '9999-99-99',date_sub('TD_DATE',1),d.end_date) as end_date,
       start_date
from zxedu_dwd.fact_customer_relationship as d
left join (select id from zxedu_ods.customer_relationship where dt = 'TD_DATE') as o
on d.id = o.id
union all
select id,
       create_date_time,
       update_date_time,
       deleted,
       customer_id,
       first_id,
       belonger,
       belonger_name,
       initial_belonger,
       distribution_handler,
       business_scrm_department_id,
       last_visit_time,
       next_visit_time,
       origin_type,
       itcast_school_id,
       itcast_subject_id,
       intention_study_type,
       anticipat_signup_date,
       level,
       creator,
       current_creator,
       creator_name,
       origin_channel,
       comment,
       first_customer_clue_id,
       last_customer_clue_id,
       process_state,
       process_time,
       payment_state,
       payment_time,
       signup_state,
       signup_time,
       notice_state,
       notice_time,
       lock_state,
       lock_time,
       itcast_clazz_id,
       itcast_clazz_time,
       payment_url,
       payment_url_time,
       ems_student_id,
       delete_reason,
       deleter,
       deleter_name,
       delete_time,
       course_id,
       course_name,
       delete_comment,
       close_state,
       close_time,
       appeal_id,
       tenant,
       total_fee,
       belonged,
       belonged_time,
       belonger_time,
       transfer,
       transfer_time,
       follow_type,
       transfer_bxg_oa_account,
       transfer_bxg_belonger_name,
       '9999-99-99' as end_date,
       substring(create_date_time,1,4) as year_code,
       substring(create_date_time,1,7) as month_code,
       substring(create_date_time,1,10) as date_code,
       dt as start_date
from zxedu_ods.customer_relationship where dt = 'TD_DATE';

insert overwrite table zxedu_dwd.fact_customer_relationship
select * from zxedu_dwd.fact_customer_relationship_tmp;
"
